package me.wz.util.sdbc.spring;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import me.wz.util.exception.RTException;
import me.wz.util.sdbc.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;

import javax.persistence.Column;
import javax.sql.DataSource;
import java.beans.IntrospectionException;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;

@Component
public class Sdbc extends TransactionSupport implements ISdbc {

    @Autowired(required=false)
    protected NamedParameterJdbcTemplate jdbc;

    public NamedParameterJdbcTemplate getJdbc() { return jdbc; }
    public void setJdbc(NamedParameterJdbcTemplate jdbc) { this.jdbc = jdbc; }

    private IDialect dialect;
    public IDialect getDialect() {
        try {
            if( dialect == null ) {
                synchronized (this) {
                    if( dialect == null ) {
                        Connection connection = getConnection();
                        try {
                            String dbName = connection.getMetaData().getDatabaseProductName();
                            dialect = Dialect.getDialect(dbName);
                            Assert.notNull(dialect, "dialect can't be null." + dbName);
                        }
                        finally {
                            try { if( connection != null ){ connection.close(); } } catch(Exception ex){}// ignore
                        }
                    }
                }
            }
            return dialect;
        }
        catch(Exception e) {
            throw new RTException(e.getMessage(), e);
        }
    }
    public void setDialect(IDialect dialect) { this.dialect = dialect; }

    private String onPrepareStatement(String sql){
        AtomicBoolean isPrepared = new AtomicBoolean(false);
        String ret = Intcpt.onPrepareStatement(sql, isPrepared);
        if( logger.isDebugEnabled() && isPrepared.get() ) {
            logger.debug("[{}]{} => {}", !sql.equals(ret), sql, ret);
        }
        return ret;
    }

    private Logger logger = LoggerFactory.getLogger(getClass());

    @SuppressWarnings("unchecked")
    public <T>List<T> select(T bean) {
        try {
            String select = onPrepareStatement(SQLBuilder.select(bean));
            if( logger.isDebugEnabled() ) {
                logger.debug(select + ";" + toJson(bean));
            }
            return jdbc.query(select, new BeanPropertySqlParameterSource(bean), new BeanPropertyRowMapper<T>((Class<T>) bean.getClass()));
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    public <T>List<T> select(Class<T> clazz) {
        try {
            String select = onPrepareStatement(SQLBuilder.select(clazz));
            if( logger.isDebugEnabled() ) {
                logger.debug(select);
            }
            return jdbc.query(select, (Map<String, ?>) null, new BeanPropertyRowMapper<T>(clazz));
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    public List<Map<String, Object>> select(String select) {
        return select(select, null);
    }
    public List<Map<String, Object>> select(String select, Map<String, Object> params) {
        select = onPrepareStatement(select);
        if( logger.isDebugEnabled() ) {
            logger.debug(select + ";" + toJson(params));
        }
        return jdbc.queryForList(select, params);
    }
    public <T> List<T> select(String select, Map<String, Object> params, Class<T> clazz) {
        select = onPrepareStatement(select);
        if( logger.isDebugEnabled() ) {
            logger.debug(select + ";" + toJson(params));
        }
        return isPrimitive(clazz)
                ? jdbc.queryForList(select, params, clazz)
                : jdbc.query(select, params, BeanPropertyRowMapper.newInstance(clazz))
                ;
    }

    public <T>List<T> list(String select, Map<String, Object> params, Class<T> clazz) { return select(select, params, clazz); }
    public List<Map<String, Object>> list(String select, Map<String, Object> params){ return select(select, params); }
    public List<Map<String, Object>> list(String select){ return select(select); }
    public <T>List<T> list(Class<T> clazz) {
        return select(clazz);
    }
    public <T>List<T> list(T bean) {
        return select(bean);
    }


    @Override
    public <T> Page<T> page(T bean, Pagination pagination) {
        try {
            Page<T> page = new Page<T>(pagination);
            String select = onPrepareStatement(SQLBuilder.select(bean));
            IDialect dialect = getDialect();
            String query = dialect.query(select, page);
            String count = dialect.count(select, page);
            if( logger.isDebugEnabled() ) {
                logger.debug(query + ";" + toJson(bean));
            }
            List<T> items = jdbc.query(query, new BeanPropertySqlParameterSource(bean), new BeanPropertyRowMapper<T>((Class<T>) bean.getClass()));
            if( !page.getHasMoreMode() ) {
                int total = jdbc.queryForObject(count, new BeanPropertySqlParameterSource(bean), Integer.class);
                page.setPageTotal(total / page.getPageSize() + (total % page.getPageSize() > 0 ? 1 : 0));
                page.setItems(items);
                page.setTotal(total);
            }
            else {
                page.setPageTotal(items.size() > page.getPageSize() ? page.getPageNum() + 1 : page.getPageNum());
                page.setItems(items.size() <= page.getPageSize() ? items : items.subList(0, items.size() - 1));
                page.setTotal(page.getPageSize() * (page.getPageNum() - 1) + items.size());
            }
            return dialect.revert(page);
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    @Override
    public <T> Page<T> page(Class<T> clazz, Pagination pagination) {
        try {
            Page<T> page = new Page<T>(pagination);
            String select = onPrepareStatement(SQLBuilder.select(clazz));
            IDialect dialect = getDialect();
            String query = dialect.query(select, page);
            String count = dialect.count(select, page);
            if( logger.isDebugEnabled() ) {
                logger.debug(query + ";");
            }
            List<T> items = jdbc.query(query, (Map<String, ?>) null, new BeanPropertyRowMapper<T>(clazz));
            if( !page.getHasMoreMode() ) {
                int total = jdbc.queryForObject(count, (Map<String, ?>) null, Integer.class);
                page.setPageTotal(total / page.getPageSize() + (total % page.getPageSize() > 0 ? 1 : 0));
                page.setItems(items);
                page.setTotal(total);
            }
            else {
                page.setPageTotal(items.size() > page.getPageSize() ? page.getPageNum() + 1 : page.getPageNum());
                page.setItems(items.size() <= page.getPageSize() ? items : items.subList(0, items.size() - 1));
                page.setTotal(page.getPageSize() * (page.getPageNum() - 1) + items.size());
            }
            return dialect.revert(page);
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    @Override
    public Page<Map<String, Object>> page(String select, Pagination pagination) {
        return page(select, (Map<String, Object>) null, pagination);
    }
    @Override
    public Page<Map<String, Object>> page(String select, Map<String, Object> params, Pagination pagination) {
        select = onPrepareStatement(select);
        Page<Map<String, Object>> page = new Page<Map<String, Object>>(pagination);
        IDialect dialect = getDialect();
        String query = dialect.query(select, page);
        String count = dialect.count(select, page);
        if( logger.isDebugEnabled() ) {
            logger.debug(query + ";" + params);
        }
        List<Map<String, Object>> items = jdbc.queryForList(query, params);
        if( !page.getHasMoreMode() ) {
            int total = jdbc.queryForObject(count, params, Integer.class);
            page.setPageTotal(total / page.getPageSize() + (total % page.getPageSize() > 0 ? 1 : 0));
            page.setItems(items);
            page.setTotal(total);
        }
        else {
            page.setPageTotal(items.size() > page.getPageSize() ? page.getPageNum() + 1 : page.getPageNum());
            page.setItems(items.size() <= page.getPageSize() ? items : items.subList(0, items.size() - 1));
            page.setTotal(page.getPageSize() * (page.getPageNum() - 1) + items.size());
        }
        return dialect.revert(page);
    }
    @Override
    public <T> Page<T> page(String select, Map<String, Object> params, Class<T> clazz, Pagination pagination) {
        select = onPrepareStatement(select);
        Page<T> page = new Page<T>(pagination);
        IDialect dialect = getDialect();
        String query = dialect.query(select, page);
        String count = dialect.count(select, page);
        if( logger.isDebugEnabled() ) {
            logger.debug(query + ";" + params);
        }
        List<T> items = jdbc.query(query, params, BeanPropertyRowMapper.newInstance(clazz));
        if( !page.getHasMoreMode() ) {
            int total = jdbc.queryForObject(count, params, Integer.class);
            page.setPageTotal(total / page.getPageSize() + (total % page.getPageSize() > 0 ? 1 : 0));
            page.setItems(items);
            page.setTotal(total);
        }
        else {
            page.setPageTotal(items.size() > page.getPageSize() ? page.getPageNum() + 1 : page.getPageNum());
            page.setItems(items.size() <= page.getPageSize() ? items : items.subList(0, items.size() - 1));
            page.setTotal(page.getPageSize() * (page.getPageNum() - 1) + items.size());
        }
        return dialect.revert(page);
    }

    public <T>T get(T bean, boolean isUnique) {
        List<T> rets = select(bean);
        if( isUnique && rets.size() > 1 ) {
            throw new RTException("incorrect record count: expected 1, actual " + rets.size());
        }
        return rets.isEmpty() ? null : rets.get(0);
    }
    public <T>T get(T bean) {
        return get(bean, false);
    }
    public <T>T get(Class<T> clazz, Object id) {
        try {
            Assert.notNull(id, "id can't be null");

            String select = onPrepareStatement(SQLBuilder.select(clazz, id));
            if( logger.isDebugEnabled() ) {
                logger.debug(select + ";" + toJson(id));
            }

            List<T> rets = null;
            if( isPrimitive(id) ) {
                rets = jdbc.getJdbcOperations().query(select, new Object[] {id}, new BeanPropertyRowMapper<T>(clazz));
            }
            else if( Map.class.isInstance(id) ) {
                rets = jdbc.query(select, (Map<String, ?>)id, new BeanPropertyRowMapper<T>(clazz));
            }
            else {
                rets = jdbc.query(select, new BeanPropertySqlParameterSource(id), new BeanPropertyRowMapper<T>(clazz));
            }
            if( rets.size() > 1 ) {
                throw new RTException("incorrect record count: expected 1, actual " + rets.size());
            }
            return rets.isEmpty() ? null : rets.get(0);
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    @Override
    public <T> T get(String select, Map<String, Object> params, Class<T> clazz, boolean isUnique) {
        List<T> rets = select(select, params, clazz);
        if( isUnique && rets.size() > 1 ) {
            throw new RTException("incorrect record count: expected 1, actual " + rets.size());
        }
        return rets.isEmpty() ? null : rets.get(0);
    }

    public <T>int insert(T bean) {
        try {
            String sql = onPrepareStatement(SQLBuilder.insert(bean));
            if( logger.isDebugEnabled() ) {
                logger.debug(sql + ";" + toJson(bean));
            }
            KeyHolder keyHolder = new GeneratedKeyHolder();
            int ret = jdbc.update(sql, new BeanPropertySqlParameterSource(bean), keyHolder);
            if( keyHolder.getKeys() != null ) {
                setPkAfterInsert(bean, keyHolder.getKeys());
            }
            return ret;
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    public <T>int[] insert(List<T> beans) {
        int[] rets = new int[beans.size()];
        for(int i=0; i<beans.size(); i++) {
            rets[i] = insert(beans.get(i));
        }
        return rets;
    }

    public <T>int delete(T bean) {
        try {
            String sql = onPrepareStatement(SQLBuilder.delete(bean));
            if( logger.isDebugEnabled() ) {
                logger.debug(sql + ";" + toJson(bean));
            }
            return jdbc.update(sql, new BeanPropertySqlParameterSource(bean));
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    public <T>int update(T bean) {
        try {
            String sql = onPrepareStatement(SQLBuilder.update(bean));
            if( logger.isDebugEnabled() ) {
                logger.debug(sql + ";" + toJson(bean));
            }
            return jdbc.update(sql, new BeanPropertySqlParameterSource(bean));
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    public int update(String sql) {
        return update(sql, (Map<String,?>)null);
    }
    public int update(String sql, Map<String, ?> params) {
        try {
            sql = onPrepareStatement(sql);
            if( logger.isDebugEnabled() ) {
                logger.debug(sql + ";" + toJson(params));
            }
            return jdbc.update(sql, params);
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }
    public int[] update(String sql, Map<String, ?>[] batchValues) {
        try {
            sql = onPrepareStatement(sql);
            if( logger.isDebugEnabled() ) {
                logger.debug(sql + ";" + toJson(batchValues));
            }
            return jdbc.batchUpdate(sql, batchValues);
        }
        catch (Exception e) {
            throw DataAccessException.class.isInstance(e) ? (DataAccessException)e : new RTException(e.getMessage(), e);
        }
    }


    public int execute(String sql){ return update(sql); }
    public int execute(String sql, Map<String, ?> params){
        //return update(sql, params);
        return execute(connection -> {
            CallableStatement stmt = null;
            try {
                SQLParser SQLParser = new SQLParser(onPrepareStatement(sql), params);
                if( logger.isDebugEnabled() ) {
                    logger.debug(SQLParser.getSql() + ";" + toJson(SQLParser.getValues()));
                }
                stmt = connection.prepareCall(SQLParser.getSql());
                for(int i = 0; i < SQLParser.getValues().length; ++i) {
                    Object param = SQLParser.getValues()[i];
                    if( param == null ) {
                        stmt.setNull(i + 1, Types.VARCHAR);
                    }
                    else if( param instanceof OutParameter ) {
                        ((OutParameter)param).register(stmt, i + 1);
                    }
                    else {
                        stmt.setObject(i + 1, param);
                    }
                }
                stmt.execute();
                int rows = stmt.getUpdateCount();
                for(int i = 0; i < SQLParser.getValues().length; ++i) {
                    Object param = SQLParser.getValues()[i];
                    if( param instanceof OutParameter ) {
                        ((OutParameter)param).setValue(stmt, i + 1);
                        ((Map<String, Object>)params).put(SQLParser.getNames()[i], param);
                    }
                }
                return rows;
            }
            finally {
                if( stmt != null ){ stmt.close(); }
            }
        });
    }
    public <T>T execute(IExecute<T> execute) {
        Connection connection = null;
        try {
            //connection = getConnection();
            connection = DataSourceUtils.getConnection(getDataSource());
            return execute.execute(connection);
        }
        catch(Exception e) {
            if( !DataSourceUtils.isConnectionTransactional(connection, getDataSource()) ) {
                DataSourceUtils.releaseConnection(connection, getDataSource());
                //try { if( connection != null ){ connection.close(); } } catch(Exception ex){}// ignore
            }
            throw new RTException(e.getMessage(), e);
        }
    }

    private boolean isPrimitive(Object o) { return SQLBuilder.isPrimitive(o); }
    private boolean isPrimitive(Class<?> clazz) { return SQLBuilder.isPrimitive(clazz); }

    private static Gson gson = new GsonBuilder()
            .excludeFieldsWithModifiers(Modifier.FINAL, Modifier.TRANSIENT, Modifier.STATIC)
            .serializeNulls()
            .create();
    private String toJson(Object object) {
        return gson.toJson(object);
    }

    protected DataSource getDataSource() { return ((JdbcTemplate) jdbc.getJdbcOperations()).getDataSource(); }
    protected Connection getConnection() throws SQLException { return getDataSource().getConnection(); }

    protected <T>void setPkAfterInsert(T bean, Map<String, Object> pks) throws IntrospectionException, IllegalAccessException {
        me.wz.util.Assert.notNull(bean, "bean can't be null");
        me.wz.util.Assert.notNull(pks, "pks can't be null");
        Metadata metadata = SQLBuilder.getMetadata(bean.getClass());

        List<String> keys = new ArrayList<>(pks.keySet());
        List<Field> fields = metadata.getIdFields();
        for(int i=0, n=fields.size(); i<n; i++) {
            Field field = fields.get(i);
            Object value = field.get(bean);
            if( value == null ) {
                String name = metadata.getColumn(field);
                if( isPrimitive(field.getType()) ){
                    if( keys.contains(name) ) {
                        field.set(bean, pks.get(name));
                    }
                    else if( keys.size() == fields.size() ) {
                        //Assert.isTrue(keys.size() == 1, "not support.");
                        Object kval = pks.get(keys.get(i));
                        if( kval != null ) {
                            field.set(bean, cast(kval, field.getType()));
                        }
                    }
                    else {
                        throw new RTException("can't match keys num.holder.key:" + String.join(",", keys));
                    }
                }
                else {
                    throw new RTException("pk only support primitive type." + field.getName() + ":" + field.getType().getName());
                    //复合主键
                    /*
                    if( value == null ) {
                        value = field.getType().newInstance();
                    }
                    setPkAfterInsert(value, pks);
                    break;
                    */
                }
            }
        }
    }

    protected <T>T cast(Object val, Class<T> clazz) {
        if( val == null ) {
            return null;
        }
        if( val.getClass() == clazz ) {
            return (T) val;
        }
        if( clazz == String.class ) {
            return (T) val.toString();
        }
        else if( val instanceof Number ) {
            Object ret = null;
            if( Integer.class.isAssignableFrom(clazz) ) {
                ret = ((Number) val).intValue();
            }
            else if( Long.class.isAssignableFrom(clazz) ) {
                ret = ((Number) val).longValue();
            }
            else if( Short.class.isAssignableFrom(clazz) ) {
                ret = ((Number) val).shortValue();
            }
            else if( Float.class.isAssignableFrom(clazz) ) {
                ret = ((Number) val).floatValue();
            }
            else if( Double.class.isAssignableFrom(clazz) ) {
                ret = ((Number) val).doubleValue();
            }
            else if( Byte.class.isAssignableFrom(clazz) ) {
                ret = ((Number) val).byteValue();
            }
            else {
                ret = val;
            }
            return (T) ret;
        }
        return (T) val;
    }


    SQLBuilder SQLBuilder = new SQLBuilder(){
        @Override
        protected Metadata newMetadata(Class<?> clazz) throws IntrospectionException {
            return new Metadata.RDSMetadata(clazz){
                @Override
                protected Connection getConnection() throws SQLException {
                    return Sdbc.this.getConnection();
                }
            }.initialize();
        }
//        @Override
//        public String insert(Object bean) throws IntrospectionException, IllegalArgumentException, IllegalAccessException {
//            String sql = super.insert(bean);
//            if( logger.isDebugEnabled() ) {
//                logger.debug(sql + ";" + toJson(bean));
//            }
//            return sql;
//        }
//        @Override
//        public String update(Object bean) throws IntrospectionException, IllegalArgumentException, IllegalAccessException {
//            String sql = super.update(bean);
//            if( logger.isDebugEnabled() ) {
//                logger.debug(sql + ";" + toJson(bean));
//            }
//            return sql;
//        }
//        @Override
//        public String delete(Object bean) throws IntrospectionException, IllegalArgumentException, IllegalAccessException {
//            String sql = super.delete(bean);
//            if( logger.isDebugEnabled() ) {
//                logger.debug(sql + ";" + toJson(bean));
//            }
//            return sql;
//        }
//        @Override
//        public String select(Object bean) throws IntrospectionException, IllegalArgumentException, IllegalAccessException {
//            String sql = super.select(bean);
//            if( logger.isDebugEnabled() ) {
//                logger.debug(sql + ";" + toJson(bean));
//            }
//            return sql;
//        }
//        @Override
//        public String select(Class<?> clazz, Object param) throws IntrospectionException, IllegalArgumentException, IllegalAccessException {
//            String sql = super.select(clazz, param);
//            if( logger.isDebugEnabled() ) {
//                logger.debug(sql + ";" + toJson(param));
//            }
//            return sql;
//        }
    };

    public class BeanPropertyRowMapper<T> extends org.springframework.jdbc.core.BeanPropertyRowMapper<T> {

        public BeanPropertyRowMapper(Class<T> mappedClass) {
            super.initialize(mappedClass);
        }

        @Override
        protected String underscoreName(String name) {
            try {
                Field field = this.getMappedClass().getDeclaredField(name);
                Column column = field.getAnnotation(Column.class);
                if( column != null ) {
                    return lowerCaseName(column.name());
                }
            }
            catch(Exception e) {
                logger.warn(e.getMessage(), e);
            }
            return super.underscoreName(name);
        }
    }
}
